﻿using System;

using System.Collections.Generic;
using System.Text;
using System.Data.SQLite;
using System.Data;

namespace WABDCUI
{
    class DBConfiguration
    {
        private const string DATABASE_NAME = @"WABDC.s3";

        public static string getDataSource()
        {
            return Helper.getDataDir() + "\\" + DATABASE_NAME;
        }
    }

    public class EntityDao
    {
        public static int update(string sql)
        {
            SQLiteConnection conn = new SQLiteConnection(String.Format("data source={0}", DBConfiguration.getDataSource()));
            conn.Open();
            SQLiteCommand cmdUpdate = new SQLiteCommand(conn);
            cmdUpdate.CommandText = sql;
            int count = cmdUpdate.ExecuteNonQuery();
            conn.Close();
            return count;
        }

        public delegate object GetQueryObject(SQLiteDataReader reader);

        public static object query(string sql, GetQueryObject callback)
        {
            SQLiteConnection conn = new SQLiteConnection(String.Format("data source={0}", DBConfiguration.getDataSource()));
            conn.Open();
            SQLiteCommand cmdQuery = new SQLiteCommand(conn);
            cmdQuery.CommandText = sql;
            SQLiteDataReader reader = cmdQuery.ExecuteReader();

            object r = callback.Invoke(reader);
            conn.Close();
            return r;
        }

        private static object getObject(SQLiteDataReader reader)
        {
            reader.Read();
            if (!reader.IsDBNull(0))
            {
                return reader.GetValue(0);
            }
            else
            {
                return null;
            }
        }

        private static object getInt32(SQLiteDataReader reader)
        {
            reader.Read();
            if (!reader.IsDBNull(0))
            {
                return reader.GetInt32(0);
            }
            else
            {
                return 0;
            }
        }

        public static object unique(string sql)
        {
            return query(sql, getObject);
        }

        public static int count(string sql)
        {
            return (int)query(sql, getInt32);
        }
    }

    public class BookDao : EntityDao
    {
        private static Book getBook(string sql)
        {
            List<Book> books = getBooks(sql);
            if (books.Count == 0) return null;
            else return books[0];
        }

        private static object toBooks(SQLiteDataReader reader)
        {
            List<Book> books = new List<Book>();
            while (reader.Read())
            {
                Book book = new Book();
                book.Id = reader.GetInt32(0);
                book.Name = reader.GetString(1);
                books.Add(book);
            }
            return books;
        }

        private static List<Book> getBooks(string sql)
        {
            return (List<Book>)query(sql, toBooks);
        }

        public static Book find(int id)
        {
            return getBook(String.Format(
                @"select id, name from books where id={0}", 
                id
            ));
        }

        public static List<Book> findAll()
        {
            return getBooks(@"select id, name from books");
        }

        public static int count()
        {
            return count("select count(*) from books");
        }
    }

    public class LessonDao : EntityDao
    {
        private static Lesson getLesson(string sql)
        {
            List<Lesson> lessons = getLessons(sql);
            if (lessons.Count == 0) return null;
            else return lessons[0];
        }

        private static object toLessons(SQLiteDataReader reader)
        {
            List<Lesson> lessons = new List<Lesson>();
            while (reader.Read())
            {
                Lesson lesson = new Lesson();
                lesson.Id = reader.GetInt32(0);
                lesson.Name = reader.GetString(1);
                lesson.Book = BookDao.find(reader.GetInt32(2));
                lesson.NumberOfWords = reader.GetInt32(3);
                lessons.Add(lesson);
            }
            return lessons;
        }

        private static List<Lesson> getLessons(string sql) 
        {
            return (List<Lesson>)query(sql, toLessons);
        }

        public static Lesson find(int id)
        {
            return getLesson(String.Format(
                    @"select id, name, book_id, number_of_words 
                      from lessons 
                      where id={0}",
                    id
            ));
        }

        public static Lesson findByBookAndName(int bookId, string name)
        {
            return getLesson(String.Format(
                    @"select id, name, book_id, number_of_words
                      from lessons 
                      where book_id={0} and name='{1}'",
                    bookId,
                    name
            ));
        }

        public static List<Lesson> findByBook(Book book)
        {
            return getLessons(String.Format(
                @"select id, name, book_id, number_of_words
                from lessons 
                where book_id={0}
                order by cast(name as int)", 
                book.Id
            ));
        }

        public static List<Lesson> findByReciteId(int reciteId)
        {
            return getLessons(String.Format(
                @"select id, name, book_id, number_of_words
                from lessons 
                where id in (select lesson_id from recite_lesson where recite_id={0})",
                reciteId
            ));
        }

        public static int count()
        {
            return count("select count(*) from lessons");
        }
    }

    public class ReciteDao : EntityDao
    {
        private static List<Recite> toRecites(SQLiteDataReader reader)
        {
            List<Recite> recites = new List<Recite>();
            while (reader.Read())
            {
                Recite recite = new Recite();
                recite.Id = reader.GetInt32(0);
                recite.Name = reader.GetString(1);
                recite.CreateTime = reader.GetDateTime(2);
                recite.AccessTime = reader.GetDateTime(3);
                recite.AccessCount = reader.GetInt32(4);
                recite.NumberOfWords = reader.GetInt32(5);
                recite.Lessons = LessonDao.findByReciteId(recite.Id);
                recites.Add(recite);
            }
            return recites;
        }

        private static Recite getRecite(string sql)
        {
            List<Recite> recites = getRecites(sql);
            if (recites.Count == 0) return null;
            else return recites[0];
        }

        private static List<Recite> getRecites(string sql)
        {
            return (List<Recite>)query(sql, toRecites);
        }

        public static Recite find(int id)
        {
            return getRecite(String.Format(
                    @"select id, name, create_time, access_time, access_count, number_of_words
                    from recites 
                    where id={0}",
                    id
            ));
        }

        public static Recite findByName(string name)
        {
            return getRecite(String.Format(
                    @"select id, name, create_time, access_time, access_count, number_of_words
                      from recites
                      where name='{0}'",
                    name
            ));
        }

        public static List<Recite> findAll()
        {
            return getRecites(@"
                    select id, name, create_time, access_time, access_count, number_of_words
                    from recites");
        }

        private static object getInt(SQLiteDataReader reader)
        {
            int n = 0;
            reader.Read();
            if (!reader.IsDBNull(0))
            {
                n = reader.GetInt32(0);
            }
            return n;
        }

        public static void save(Recite recite)
        {
            if (findByName(recite.Name) != null)
            {
                throw new Exception(String.Format("'{0}'已存在!", recite.Name));
            }
            //get maxId
            int maxId = (int)query(@"select max(id) from recites", getInt);
            recite.Id = maxId+1;

            string sql = "BEGIN;";
            //insert into recite_lessons
            int totalNumberOfWords = 0;
            foreach (Lesson lesson in recite.Lessons)
            {
                sql += String.Format(
                    @"insert into recite_lesson values({0}, {1});",
                    recite.Id,
                    lesson.Id
                );
                totalNumberOfWords += lesson.NumberOfWords;
            }
            //insert into recites
            sql += String.Format(
                @"insert into recites values({0}, '{1}', '{2}', '{3}', {4}, {5});",
                recite.Id,
                recite.Name,
                DateTime.Now.ToString("s").Replace('T', ' '),
                DateTime.Now.ToString("s").Replace('T', ' '),   //accessTime
                0,                                              //accessCount
                totalNumberOfWords
            );
            sql += "COMMIT;";
            update(sql);
        }

        public static void remove(Recite recite)
        {
            string sql = "BEGIN;";
            sql += String.Format(@"delete from recites where id={0};", recite.Id);
            sql += String.Format(@"delete from recite_lesson where recite_id={0};", recite.Id);
            sql += "COMMIT;";

            update(sql);
        }
    }

    public class WordDao : EntityDao
    {
        private static List<Word> toWords(SQLiteDataReader reader)
        {
            List<Word> words = new List<Word>();
            while (reader.Read())
            {
                Word word = new Word();
                word.Id = reader.GetInt32(0);
                //word.Lesson = LessonDao.find(reader.GetInt32(1));
                word.Spelling = reader.GetString(2);
                word.Pronunciation = reader.GetString(3);
                word.Description = reader.GetString(4);
                words.Add(word);
            }
            return words;
        }

        private static Word getWord(string sql)
        {
            List<Word> words = getWords(sql);
            if (words.Count == 0) return null;
            else return words[0];
        }

        private static List<Word> getWords(string sql)
        {
            return (List<Word>)query(sql, toWords);
        }

        public static List<Word> findByRecite(Recite recite)
        {
            return getWords(String.Format(@"
                select w.id, w.lesson_id, w.spelling, w.pronunciation, w.description
                from words w
                join recite_lesson rl on rl.lesson_id=w.lesson_id
                where rl.recite_id={0}
            ", recite.Id));
        }

        public static int count()
        {
            return count("select count(*) from words");
        }
    }
}
